Previous Topic

Next Topic

To create a custom table

A table is made up of data fields. You define your table's data fields so users can enter or select specific data in custom window tabs.

Caution! Make sure a table or field that is added does not have the same name as any other iMIS table or field or any of the SQL Reserved Words. If you enter an iMIS table name, a message displays informing you that you have entered an invalid name and that the iMIS table may be overwritten. See the iMIS HelpNet Community for the latest Alpha Table Layouts.

Note: If you are using multiple databases, make sure the user-defined tables have unique names. For example, if you create the Gender table for two databases, make sure the table in database 1 is called Gender1 and the table in database 2 is called Gender2.

Caution! If your custom table contains customized objects like triggers, additional indexes, or special permissions, they will be lost when the table is rebuilt.

  1. Make sure all other users exit the iMIS system.
  2. Back up your data.
  3. Log into iMIS as the database owner: MANAGER!SA (Sybase: MANAGER!DBA)
  4. From Customers, select Set up module> General, and click Additional Windows to open the Customer Setup – Additional Windows window.
  5. Click Define Tables to open the User Defined Tables window.
  6. Click New.
  7. Enter a new Table Name. The table names are case-sensitive, so plan to use all caps or initial caps consistently. Table names must be unique. Do not use existing iMIS table names or SQL Reserved Words as the name of the table.
  8. Press Tab. iMIS prompts you to verify the table creation.
  9. Click Yes.

    Note: The scrolling list box of field entries is cleared until you complete the table's heading fields.

  10. Accept the System and Access Method defaults.
  11. (Optional) Enable the Multiple Instances Allowed option if you want customers to have more than one window tab that will contain the same fields, for example, quarterly or annual surveys.

    Note: If you enable the Multiple Instances Allowed option, the Always Create and Use for Dues Pricing options are grayed out. Enabling the Always Create and Use for Dues Pricing options grays out the Multiple Instances Allowed option.

  12. (Optional) Enable the Always Create option if you want records to be created automatically for the appropriate customer types and are using the fields for Dues special pricing. Press Tab.
  13. (Optional) Enter specific customer types in the Only applies to these types field to restrict access to the demographic table, and separate customer types by commas. Leave the field blank to allow access to all customers. Press Tab.
  14. Enable the Use for Dues Pricing option if you are using the fields for Billing special pricing. Press Tab. iMIS displays a definition area for the first custom field for this table.
  15. Enter a valid SQL Field Name, and press Tab.
  16. (Multiple Instances Allowed only) If you are creating multiple instances of this table, we recommend making your first field contain the information that will distinguish the multiple instances from each other. For example, if you plan to have a table in each customer record for each year's survey data, the first field name should be Year.
  17. Select a field format from the Type drop-down list. Press Tab.
  18. (Multiple Instances Allowed only) Create multiple instance tables with a first field type of character (Char).

    Note: Lookup is unavailable for numeric user-defined fields. When defining a field that uses a general lookup/validation table, make sure you do not assign a numeric Type to the format (for example, a number).

    Note: If you assign a numeric Type format to a field, iMIS automatically formats the numbers entered in the field with commas. If you do not want a user-defined field to be automatically formatted, assign the data type to Char.

  19. Enter the field size in the Length field if it is a Character field. Press Tab.
  20. (Optional for Int, Number, or Money fields) If a decimal can be entered in the field, enter the number of decimal places to be accepted in the Dec field.
  21. In the Prompt field, enter the name of the field that will display on the custom window tab. The default is Field Name.
  22. Select the Format option for the data entered in the field on the custom window tab.
  23. (Optional) In the Validation field, enter the general lookup/validation table that will be used to validate the data at the time of data entry. To enable this field you must build the corresponding general lookup/validation table in the Set up general lookup/validation window (from Customers, select Set up tables > General, and select General lookup/validation).

    A general lookup/validation table is required for a multiple instance table or if you enable the Multi Select option.

    Note: When creating a general lookup/validation table for a multi-select field in a user-defined table, the Description field on the Set up general lookup/validation window must contain a unique value for each Code.

    Note: Lookup is unavailable for numeric user-defined fields. When defining a field that uses a general lookup/validation table, make sure you do not assign a numeric Type to the format (for example, a number).

  24. Enable the Multi Select option if you entered a table name in the Validation field and you want to allow the user to make more than one selection from the validation table.
  25. Click Save. iMIS prompts you to set up the new table with the server.
  26. Click Yes to have iMIS create the server table. This prompt is equivalent to clicking Create/Update Server Table.
  27. (Optional) Click Auto-Create Window. iMIS automatically creates a window tab with the title based on the table name. iMIS defines the table layout and includes all the fields with the Field Name used as the field label.
  28. Click OK if prompted to restart iMIS.
  29. Click Close to close the User Defined Tables window.
  30. Exit iMIS immediately.
  31. Refresh the iMIS libraries by replacing the existing imismem.lbr and imisutil.lbr files with a clean copy of them.

    Note: If you copy the library files from a CD, make sure to disable the Read only option.

See Also

Creating a Custom Table

User Defined Tables window

To modify a custom table

To delete a custom table


ASI logo 10.6 Production Release. Updated 2/24/2006 11:13:51 AM
Copyright © by Advanced Solutions International, Inc. All rights reserved.